package com.h819.core.file.excel.poi;

import org.apache.commons.collections.SetUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.*;
import java.util.Set;
import java.util.TreeSet;

import static java.lang.System.out;

/**
 * @Title: POIUtils.java
 * @Description: TODO(apache poi 工具类)
 * @author H819
 * @date 2010-4-29
 * @version V1.0
 */

/*
 * 主要参考 
 * 
 * http://poi.apache.org/spreadsheet/quick-guide.html
 * 
 * 一个封装 http://code.google.com/p/easy-poi/
 * 
 * 可以根据需要，到其中查找具体的应用方法
 */

/*
 * 说明：
 * 
 * 1. 保存 excel 内容的数据结构为 set ，这样可以过滤掉重复行，并且通过调节判断，去掉了空白行。利用
 * 
 * org.apache.commons.collections.SetUtils
 * 
 * 来保证元素按照添加顺序排序。
 * 
 * 2. excel 的 sheet,row,colum 的开始序号均是从 0 开始。
 * 
 * 3. set 的元素为一行内容，组成为
 * 
 * File=文件名:行号:列号=单元格内容(分隔符)列号=单元格内容
 * 
 * 例如 File=test.xls(分隔符)6:0=中国(分隔符)1=5000.0(分隔符)2=2006-04-24
 * 含义为：文件名为test.xls，第六行：第0列值为"中国"，第1列值为5000.0，第2列值为2006-04-24
 */
public class POIUtils extends POIBase {

	private static Logger log = Logger.getLogger(POIUtils.class);

	/**
	 * 读取 excel 文件所有内容到 set 中，去掉了相同行和空白行(自己实现了比较器)，去掉了换行符、制表符</br>
	 * 
	 * set 的元素为一行内容，组成为
	 * 
	 * 行号:列号=单元格内容;列号=单元格内容
	 * 
	 * 例如 File=test.xls(分隔符)6:0=中国(分隔符)1=5000.0(分隔符)2=2006-04-24
	 * 
	 * 含义为：文件名为test.xls，第六行：第0列值为"中国"，第1列值为5000.0，第2列值为2006-04-24
	 * 
	 * @param excelFile
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Set<String> readExcel(File excelFile) {
		HSSFWorkbook wb = null;
		try {
			InputStream inp = new FileInputStream(excelFile);
			wb = new HSSFWorkbook(new POIFSFileSystem(inp));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (wb == null)
			return null;

		// set 可以过滤重复元素。利用 SetUtils 来保证元素按照添加顺序排序
		Set<String> set = SetUtils.orderedSet(new TreeSet<String>(
				new ComparatorSet()));

		for (int i = 0; i < wb.getNumberOfSheets(); i++) {// 循环 sheet

			for (Row row : wb.getSheetAt(i)) {// 循环行
				// 跳过空白行
				if (isBlankRow(row))
					continue;

				//一行内容
				String temp = "";
				for (Cell cell : row) {// 循环列
					temp = temp + cell.getColumnIndex() + spliteValueTag
							+ convertCellToStringValue(cell) + spliteColumTag;
				}

				// 添加非空白行到 set 中
				if (!temp.equals(""))
					set.add("File=" + excelFile.getName() + spliteColumTag
							+ row.getRowNum() + spliteLineTag
							+ StringUtils.removeEnd(temp, spliteColumTag));

			}
		}
		return set;
	}

	/**
	 * 读取指定 sheet ,开始行和结束行 excel 文件内容到 set 中，去掉了相同行和空白行(自己实现了比较器)，去掉了换行符、制表符</br>
	 * 
	 * @param excelFile
	 *            待读取的文件
	 * @param sheetNum
	 *            指定 sheet 编号(从 0 开始)
	 * @param startLineNum
	 *            开始行编号(从 0 开始)
	 * @param endLineNum
	 *            结束行编号(从 0 开始)
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Set<String> readExcel(File excelFile, int sheetNum,
			int startLineNum, int endLineNum) {
		HSSFWorkbook wb = null;
		try {
			InputStream inp = new FileInputStream(excelFile);
			wb = new HSSFWorkbook(new POIFSFileSystem(inp));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (wb == null)
			return null;

		// 验证数据合法性
		if (wb.getNumberOfSheets() < sheetNum + 1) {
			log.info("无指定 sheet，超出 excel 文件范围");
			return null;
		}
		Sheet sheet1 = wb.getSheetAt(sheetNum);
		int firstRowNum = sheet1.getFirstRowNum();
		int lastRowNum = sheet1.getLastRowNum();
		// log.info("getNumberOfSheets :" + wb.getNumberOfSheets());
		// log.info("firstRowNum :" + firstRowNum);
		// log.info("lastRowNum :" + lastRowNum);

		if (firstRowNum == 0 && lastRowNum == 0) {
			log.info("指定 sheet 无数据");
			return null;
		}

		if (startLineNum < firstRowNum)
			startLineNum = firstRowNum;

		if (lastRowNum < endLineNum)
			endLineNum = lastRowNum;

		// log.info("start :" + startLineNum);
		// log.info("end :" + endLineNum);

		// set 可以过滤重复元素。利用 SetUtils 来保证元素按照添加顺序排序
		Set<String> set = SetUtils.orderedSet(new TreeSet<String>(
				new ComparatorSet()));

		// 循环 sheet
		for (Row row : sheet1) {// 循环行
			// 跳过空白行
			if (isBlankRow(row))
				continue;

			// 添加满足条件的行
			if (row.getRowNum() <= endLineNum
					&& row.getRowNum() >= startLineNum) {
				String temp = "";
				for (Cell cell : row) {// 循环列
					temp = temp + cell.getColumnIndex() + spliteValueTag
							+ convertCellToStringValue(cell) + spliteColumTag;
				}

				// 添加非空白行到 set 中
				if (!temp.equals(""))
					set.add("File=" + excelFile.getName() + spliteColumTag
							+ row.getRowNum() + spliteLineTag
							+ StringUtils.removeEnd(temp, spliteColumTag));
			}

		}

		return set;
	}

	/**
	 * 读取指定 sheet，指定行和列的 cell 值
	 * 
	 * @param excelfile
	 *            待读取的文件
	 * @param sheetNum
	 *            指定 sheet 编号(从 0 开始)
	 * @param lineNum
	 *            行编号(从 0 开始)
	 * @param columNum
	 *            列编号(从 0 开始)
	 * @return
	 */
	public static String readExcelCellValue(File excelfile, int sheetNum,
			int lineNum, int columNum) {
		HSSFWorkbook wb = null;
		try {
			InputStream inp = new FileInputStream(excelfile);
			wb = new HSSFWorkbook(new POIFSFileSystem(inp));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (wb == null)
			return null;

		// 验证数据合法性
		if (wb.getNumberOfSheets() < sheetNum + 1) {
			log.info("无指定 sheet，超出 excel 文件范围");
			return null;
		}
		Sheet sheet1 = wb.getSheetAt(sheetNum);
		int firstRowNum = sheet1.getFirstRowNum();
		int lastRowNum = sheet1.getLastRowNum();
		// log.info("getNumberOfSheets :" + wb.getNumberOfSheets());
		// log.info("firstRowNum :" + firstRowNum);
		// log.info("lastRowNum :" + lastRowNum);

		if (firstRowNum == 0 && lastRowNum == 0) {
			log.info("指定 sheet 无数据");
			return null;
		}

		if (lineNum < firstRowNum || lineNum > lastRowNum) {
			log.info("行数越界");
			return null;
		}

		Cell cell = sheet1.getRow(lineNum).getCell(columNum);
		if (cell != null)
			return convertCellToStringValue(cell);
		else
			return null;
	}

	/**
	 * 读取指定 sheet ,指定行的 excel 文件内容为 字符串，去掉了换行符、制表符</br>
	 * 
	 * @param excelFile
	 *            待读取的文件
	 * @param sheetNum
	 *            指定 sheet 编号(从 0 开始)
	 * @param lineNum
	 *            指定行编号(从 0 开始)
	 * @return
	 */
	public static String readExcelLineValue(File excelFile, int sheetNum,
			int lineNum) {
		String lineString = "";
		// 只有一行
		for (String s : readExcel(excelFile, sheetNum, lineNum, lineNum)) {
			lineString = s;
		}
		return lineString;
	}

	/**
	 * 没有找到相关的 api
	 */
	private static void copy() {

	}

	/**
	 * 没有找到相关的 api
	 */
	private static void insert() {

	}

	/**
	 * 写 excel 例子。因为写的需求每个项目都不一样，所以这里不给通用的函数，仅给个例子，项目可根据这个函数来修改。
	 */
	private static void writeExample() {
		// create a new workbook
		Workbook wb = new HSSFWorkbook();

		// 创建一个WorkBook，从指定的文件流中创建，即上面指定了的文件流
		// FileInputStream readFile = new FileInputStream(templatefilepath);
		// Workbook wb = new HSSFWorkbook(readFile);
		// 获取指定的 sheet 对象
		// Sheet sheet = wb.getSheetAt(0);

		// create a new sheet
		Sheet s = wb.createSheet("new sheet");
		// declare a row object reference
		Row r = null;
		// declare a cell object reference
		Cell c = null;

		// create a sheet with 30 rows (0-29)
		int rownum;
		for (rownum = (short) 0; rownum < 30; rownum++) {
			// create a row
			r = s.createRow(rownum);
			// create 10 cells (0-9) (the += 2 becomes apparent later
			for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) {
				c = r.createCell(cellnum);
				c.setCellValue("");
			}
		}

		try {
			FileOutputStream out = new FileOutputStream("d:\\workbook.xls");
			wb.write(out);
			out.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * set 中由于有复杂的分隔符，行数据不直观，此处为替换分隔符之后的set，仅为测试用，实际使用时应该用自定义的分隔符。
	 * 
	 * @param set
	 */
	public static void testExcelValue(Set<String> set) {

		for (String s : set) {
			s = StringUtils.replace(s, spliteValueTag, "=");
			s = StringUtils.replace(s, spliteColumTag, ";");
			out.println(s);
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		// e.testPrintSingleMap(e.readExcelColumValue(new
		// File("d://readset.xls"),
		// 0, 0));

		 Set<String> set = POIUtils.readExcel(new File("D://download//1.xls"));
		 StringBuffer sf = new StringBuffer();
		 for (String s : set){
			 sf.append(s+"\n");
		 }
		
		 out.println(sf.toString());
		 
		// Set<String> set1 = e.readExcel(new File("D://shenzhen//10000.xls"));
		// try {
		// FileUtils.writeLines(new File("d:///excel.txt"), set1);
		// } catch (IOException e1) {
		// // TODO Auto-generated catch block
		// e1.printStackTrace();
		// }
		// for (String s : set1) {
		// out.println(s);
		// s = POIUtils.getColumValue(s, 0);
		// out.println(s);
		// }

		// POIUtils.testPrintExcelValue(set1);

		//POIUtils.testExcelValue(set);

	}
}
